package jsu.hx.lost.Mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import jsu.hx.lost.Model.dto.FindItemDTO;
import jsu.hx.lost.Model.entity.LostItem;
import jsu.hx.lost.Model.dto.LostItemDTO;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;
@Mapper
public interface LostMapper extends BaseMapper<LostItem>{
    // LostMapper.java 新增方法
    @Select("SELECT li.id, li.created_at, li.item_name, li.image, li.item_category, " +
            "li.lost_location,li.locate, li.lost_time, li.detailed_description, li.locate, " +
            "up.username, up.full_name, up.phone " +
            "FROM lost_items li " +
            "JOIN user_profiles up ON li.user_id = up.id " +
            "WHERE li.user_id = #{userId} AND li.is_deleted = 0 AND up.is_deleted = 0")
    List<LostItemDTO> selectLostItemDTOsByUserId(@Param("userId") Long userId);
    // 自定义模糊查询方法
    List<LostItemDTO> selectByFuzzySearch(@Param("itemName") String itemName,
                                          @Param("lostLocation") String lostLocation,
                                          @Param("lostTime") Date lostTime,
                                          @Param("username") String username);

    // 修改分页查询方法，使用日期范围匹配
    @Select("SELECT li.id, li.created_at, li.item_name, li.image, li.item_category, " +
            "li.lost_location, li.lost_time, li.detailed_description, li.locate, " +
            "up.username, up.full_name, up.phone " +
            "FROM lost_items li " +
            "JOIN user_profiles up ON li.user_id = up.id " +
            "WHERE li.is_deleted = 0 AND up.is_deleted = 0 " +
            "AND (#{itemName} IS NULL OR li.item_name LIKE CONCAT('%', #{itemName}, '%')) " +
            "AND (#{lostLocation} IS NULL OR li.lost_location LIKE CONCAT('%', #{lostLocation}, '%')) " +
            "AND (#{lostTime} IS NULL OR DATE(li.lost_time) = DATE(#{lostTime})) " + // 改为日期匹配
            "AND (#{username} IS NULL OR up.username LIKE CONCAT('%', #{username}, '%')) " +
            "ORDER BY ${sortField} ${sortOrder} " +
            "LIMIT #{offset}, #{limit}")
    List<LostItemDTO> selectLostItemDTOsByPage(
            @Param("offset") int offset,
            @Param("limit") int limit,
            @Param("itemName") String itemName,
            @Param("lostLocation") String lostLocation,
            @Param("lostTime") Date lostTime, // 保持为Date类型
            @Param("username") String username,
            @Param("sortField") String sortField,
            @Param("sortOrder") String sortOrder
    );
    @Select("SELECT li.id, li.created_at, li.item_name, li.image, li.item_category, li.lost_location, li.lost_time, li.detailed_description,li.locate, up.username, up.full_name, up.phone " +
            "FROM lost_items li " +
            "JOIN user_profiles up ON li.user_id = up.id " +
            "WHERE li.is_deleted = 0 AND up.is_deleted = 0")
    List<LostItemDTO> selectLostItemDTOs();

    @Select("SELECT li.id, li.created_at, li.item_name, li.image, li.item_category, li.lost_location, li.locate,li.lost_time, li.detailed_description, up.username, up.full_name, up.phone " +
            "FROM lost_items li " +
            "JOIN user_profiles up ON li.user_id = up.id " +
            "WHERE li.id = #{id} AND li.is_deleted = 0 AND up.is_deleted = 0")
    LostItemDTO selectLostItemDTOById(@Param("id") Long id);
    // LostMapper.java
    @Select("SELECT COUNT(*) FROM lost_items li " +
            "JOIN user_profiles up ON li.user_id = up.id " +
            "WHERE li.is_deleted = 0 AND up.is_deleted = 0")
    long selectLostItemTotalCount();
    @Select("SELECT li.id, li.created_at, li.item_name, li.image, li.item_category, " +
            "li.lost_location, li.lost_time, li.detailed_description, " +
            "up.username, up.full_name, up.phone " +
            "FROM lost_items li " +
            "JOIN user_profiles up ON li.user_id = up.id " +
            "WHERE li.is_deleted = 0 AND up.is_deleted = 0 " +
            "ORDER BY li.created_at DESC LIMIT #{limit}")
    List<LostItemDTO> selectLatestLostItems(@Param("limit") int limit);
}

